<?php
class import_salary extends Public_Controller{
	public $profiles;
	public $cache_profile = 'emp_profile/profile_';
	function __construct(){
		parent::__construct();		
		ini_set('memory_limit','512M');
		//$this->check_permissions();		
		$this->load->model("number_m");
		$this->load->model("import_salary_m","im_salary");
		$this->year  = "";
		for($i=date("Y")-10;$i <= date("Y");$i++)
		{
			$this->year[$i] = $i;
		}
		$this->template->set_layout('import_salary.html');
		//set_time_limit(300);
		$this->hourscome_banle_ca1  = strtotime("8:00"); // gio bat dau lam cua ban le ca 1;
		$this->hoursout_banle_ca1   = strtotime("15:00"); // gio ket thuc lam cua ban le ca 2;
		$this->hourscome_banle_ca2  = strtotime("14:30"); // gio bat dau cua ban le ca 2;
		$this->hoursout_banle_ca2   = strtotime("21:30"); // gio ket thuc lam ban le ca 2; 
		$this->list_accessexcel = array();
		//date_default_timezone_set('Asia/Ho_Chi_Minh');
	}
	public function index(){
		$month_select = $this->number_m->month_select();
	 	$year_select  = $this->number_m->year_select(2000,date("Y"));
		//add post values to base_where if f_module is posted
		$year  = ($this->input->get("year")) ?$this->input->get("year"):date("Y");
		$month = ($this->input->get("month"))?$this->input->get("month"):date("m");
		if($month == 0){$month = 12;$year -= 1;}
		$id_user_room = "";
		if($this->current_user->group_id != 1 && $this->current_user->group_id != 2)
				$id_user_room = $this->im_salary->getid_room($this->current_user->id);
		// Create pagination links
		$filename = $this->for_list_files("./uploads/bang_cham_cong/".$year."/".$month,$id_user_room);
		$total_rows = count($filename);		
		if ($this->input->is_ajax_request())
		{
			$this->template->set_layout(false);
		}
	    $this->action 	= 'back|'.site_url('emp_profile/salary').'|'.lang('global:back');
		$this->action   .= '+ excel|'.site_url('emp_profile/import_salary/active_salary').'|'.lang('global:addfile');
		$this->template->set('admin',true)->append_css('toolbar.css');
		$this->template
			->set('action',$this->action)
			->append_css('table.css')
			->append_js('ui/jquery-ui.min.js')
			->append_css('ui/jquery-ui.min.css')
			->append_js('admin/plugins.js')
			->append_js('admin/scripts.js')
			->append_js('business/site.js')
			->append_js('chosen/chosen.jquery.min.js')
			->append_css('chosen/chosen.css')
			->title(lang('salary:list_file'))
			->set("year",$year)
			->set("month",$month)
			->set('filename',$filename)
			->set("month_select",$month_select)
			->set("year_select",$year_select)
			->append_js('filter.js');
			$this->input->is_ajax_request() ? $this->template->build($this->controller.'/partial/list') : $this->template->build($this->controller.'/index');
	}
	function for_list_files($directory = '.',$id_user_room)
	{
		$file_name = "";
		if ($directory != '.')
		{
			$directory = rtrim($directory, '/') . '/';
		}
		if ($handle = @ opendir($directory))
		{
			$i = 0;
			while (false !== ($file = readdir($handle)))
			{
				if ($file != '.' && $file != '..')
				{ 	
					if($this->current_user->group_id != 1 && $this->current_user->group_id != 2)
					{
						$id_room = explode("_",$file);	
						if(count($id_room) == 3)
							$id_room = str_replace(".xls","",str_replace(".xlsx","",$id_room[2]));
						else
							$id_room = 0;
						$name_room = $this->im_salary->name_room($id_room,$i);
						if($name_room != "" && $i == 1 && $symbol == "_" && $num == $id_user_room)
						{
							$file_name[]=array("tenfile"=>str_replace(".xls",'',$file),"phongban"=>$name_room);	
						}
						else if ($id_room == $id_user_room && $i == 1 && $name_room != "")
							$file_name[]=array("tenfile"=>str_replace(".xls",'',$file),"phongban"=>$name_room);							
						$i=1;
					}
					else
					{
						$id_room = explode("_",$file);	
						if(count($id_room) == 3)
							$id_room = str_replace(".xls","",str_replace(".xlsx","",$id_room[2]));
						else
							$id_room = 0;		
						$name_room = $this->im_salary->name_room($id_room,$i);
						$file_name[]=array("tenfile"=>str_replace(".xls",'',$file),"phongban"=>$name_room);
						$i=1;
					}
				}
				
			}
			@ closedir($handle);
		}
		return $file_name;
	}
	/* them bang cham cong excel */
	public function active_salary()
	{
	  $date_banle 	= $this->number_m->day_select();
	  $month_select = $this->number_m->month_select();
	  $year_select  = $this->number_m->year_select($this->year_start,date("Y"));
	  $date 		= $this->number_m->day_select();
	  $this->action  = 'back|'.site_url('emp_profile/import_salary').'|'.lang('global:back');
	  $this->action .= '+ update_salary|javascript:void(0)|'.lang('import:delete_salary');
	  $this->template->set('admin',true)->append_css('toolbar.css');
	  $this->template->set('action',$this->action);
	  $this->template ->set("title",lang('salary:add_table_salary'))
					  ->set("month_select",$month_select)
					  ->set("year_select",$year_select)
					  ->set("date",$date)
					  ->set("date_banle",$date_banle)
					  ->append_css("import_salary.css")
					  ->append_css('table.css')
					  ->append_js('ui/jquery-ui.min.js')
					  ->append_css('ui/jquery-ui.min.css')
					  ->append_js('admin/plugins.js')
					  ->append_js('admin/scripts.js')
					  ->append_js('business/site.js')
					  ->append_js('chosen/chosen.jquery.min.js')
					  ->append_css('chosen/chosen.css')
					  ->build($this->controller."/create_salary");		
	}
	function add()
	{
		$month 	= $_POST['month'];
		$year 	= $_POST['year'];
		$day	= $_POST["day"];
		$day_banle	= $_POST["day_banle"];
		$file 	= $_FILES["file"];
		$name_file = $year."_".$month;
		
		$this->load->library('upload');
		$url_file_goc 		 =  "./uploads/bang_cham_cong";
		$url_file_year 		 =  $url_file_goc."/$year";
		$url_file_year_month =  $url_file_year."/$month";
		$url_file_php		 =  $url_file_year_month."/day.php";
		$url_xls			 =  $url_file_year_month."/".$name_file.".xls";
		$url_xlsx			 =  $url_file_year_month."/".$name_file.".xlsx";
		$url_ghide			 =  base_url("admin_salary/delete_file/$year/$month/$day/$day_banle");
		if(empty($_FILES["file"]["name"]))
			redirect("emp_profile/import_salary/access_page/3");
		if(!is_file($url_xls) or !is_file($url_xlsx)){
			$mode = 777;
			if(!is_file($url_file_goc))
			{
				@mkdir($url_file_goc,0777);
				@chmod($url_file_goc, octdec($mode));
			}
			if(!is_file($url_file_year))
			{
				@mkdir($url_file_year,0777);
				@chmod($url_file_year, octdec($mode));
			}
			if(!is_file($url_file_year_month))
			{
				@mkdir($url_file_year_month,0777);
				@chmod($url_file_year_month, octdec($mode));
			}
			if(!empty($_FILES['file']["name"]))
			{
				@unlink($url_file_year_month."/".$year."_".$month.".xls");
				@unlink($url_file_year_month."/".$year."_".$month.".xls");
			}
			$config['upload_path'] = $url_file_year_month;
			$config['allowed_types'] = 'xlsx|xls';
			$config['max_size']	= '10000';
			$config['file_name'] = $name_file;
			$this->load->library('upload');
			$this->upload->initialize($config);
			// tao file php
			if($this->upload->do_upload("file"))
			{
				$data_file = $this->upload->data();
				$file = $url_file_php;
				$person = '$day[0]='.$day.';$day[1]='.$day_banle;
				file_put_contents($file, $person, FILE_APPEND | LOCK_EX);
				chmod($url_file_year_month."/".$data_file["file_name"],octdec(777));
				$this->list_employ_room($url_file_year_month."/".$data_file["file_name"],$month,$year,$day,$day_banle);
				$this->im_salary->update_date($year,$month,array("value1"=>$day,"value2"=>$day_banle));
				redirect("emp_profile/import_salary/access_page/0");
			}
			else
			{
				redirect("emp_profile/import_salary/access_page/3");
			}
		}
		else
		{
			redirect("emp_profile/import_salary/access_page/2");
		}
	}
	function delete_file($year,$month,$day,$day_banle) // xoa file khi da ton tai file và ghi file lại
	{
		$name_file = $year."_".$month;
		$url_file_year_month =  "./uploads/bang_cham_cong/$year/$month";
		$url_file_php		 =	"./uploads/bang_cham_cong/$year/$month/day.php";
		if(!empty($_FILES['file']["name"])) // neu co insert file excel thi xoa file xong ghi lai 
		{
			@unlink($url_file_year_month."/".$year."_".$month.".xls");
			@unlink($url_file_year_month."/".$year."_".$month.".xlsx");
			@unlink($url_file_php);	
			$list_file = $this->for_list_files("./uploads/bang_cham_cong/$year/$month/");
			if(count($list_file)>0)

				foreach($list_file as $item)
					@unlink($url_file_year_month."/".$item["tenfile"].".xls");
			$config['upload_path'] = $url_file_year_month;
			$config['allowed_types'] = 'xlsx|xls';
			$config['max_size']	= '10000';
			$config['file_name'] = $name_file;
			$this->load->library('upload');
			$this->upload->initialize($config);
			if($this->upload->do_upload("file"))
			{
				$data_file = $this->upload->data();
				$file = $url_file_php;
				$person = '$day[0]='.$day.';$day[1]='.$day_banle;
				file_put_contents($file, $person, FILE_APPEND | LOCK_EX);
				$this->list_employ_room($url_file_year_month."/".$data_file["file_name"],$month,$year);
				redirect("emp_profile/import_salary/access_page/0");
			}
			else
				redirect("emp_profile/import_salary/access_page/3");	
		}
		else // khong co file insert thi chi insert ngay cham cong
		{
			@unlink($url_file_php);
			$file = $url_file_php;
			$person = '$day='.$day;
			file_put_contents($file, $person, FILE_APPEND | LOCK_EX);
			redirect("emp_profile/import_salary/access_page/3");
		}
	}
	function access_page($err){
		switch($err)
		{
			case 0:
				$mes = lang('global:add_success');
				break;
			case 1:
				$mes = lang('global:error_file');
				break;
			case 2:	
				$mes = lang('global:file_exist');
				break;
			case 3:	
				$mes = lang('global:select_file');
				break;
			default:
				$mes = lang('global:select_file');
				break;
		}
	  $date_banle 	= $this->number_m->day_select();
	  $month_select = $this->number_m->month_select();
	  $year_select  = $this->number_m->year_select($this->year_start,date("Y"));
	  $date 		= $this->number_m->day_select();
	  $this->template->set('admin',true)->append_css('toolbar.css');
	  $this->action = 'back|'.site_url('emp_profile/import_salary').'|'.lang('global:back');
 	  $this->template ->set("title",lang('salary:add_table_salary'))
	  				  ->set("action",$this->action)
					  ->set("month_select",$month_select)
					  ->set("success",$mes)
					  ->set("year_select",$year_select)
					  ->set("date",$date)
					  ->set("date_banle",$date_banle)
					  ->append_css("import_salary.css")
					  ->append_css('table.css')
					  ->append_css('alert.css')
					  ->append_js('ui/jquery-ui.min.js')
					  ->append_css('ui/jquery-ui.min.css')
					  ->append_js('admin/plugins.js')
					  ->append_js('admin/scripts.js')
					  ->append_js('business/site.js')
					  ->append_js('chosen/chosen.jquery.min.js')
					  ->append_css('chosen/chosen.css')
					  ->build($this->controller."/create_salary");		
	}
	function list_employ_room($url="",$month="",$year="",$day=30,$day_banle=30) // lấy danh sách nhân vien theo từng từ excel
	{
		if(is_file($url)){
			$this->load->library("excel");
			$objPHPExcel = PHPExcel_IOFactory::load($url);
			$sheet = 0;
			foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) 
			{
				 $sheet ++;
				 $worksheetTitle = $worksheet->getTitle();
				 $highestRow = $worksheet->getHighestRow(); // e.g. 10
				 $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
				 $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
				 for ($row = 6; $row <= $highestRow; ++ $row) 
				 {
					 $val = "";
					 for ($col = 0; $col < 15; ++ $col) 
					 {
						$cell = $worksheet->getCellByColumnAndRow($col,$row);
						switch ($col)
						{
							case 0: // stt 
								$val["stt"] = $cell->getValue();
								break;
							case 1: // ma so nhan vien
								$val["id_em"] = $cell->getValue();
								break;
							case 2://ten nhan vien
								$val["name_em"] = $cell->getValue();
								break;
							case 3:
								$time = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'dd/mm/YYYY');
								$val["day_job"] = $time;
								break;
							case 4: // lay ra  thứ trong bang excel
								$time = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'dd/mm/YYYY');
								$val["thu"] = $time;
								break;
							case 5://ca cua nhan vien
								$val["ca"] = $cell->getValue();
								break;	
							case 6: //
								$time = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm');
								$val["come_time"] = empty($time)?"":$time;
								break;
							case 7: // thoi gian ra
								$time = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm');
								$val["out_time"] = empty($time)?"":$time;
							case 8: // tổng số phút làm việc 1 ngày
								$time = $cell->getCalculatedValue();
								$val["total_job"] = empty($time)?"":$time;
							case 9:// Tổng số phut di muộn 
								 $time = $cell->getCalculatedValue();
								 $val["ditre"] = empty($time)?"":$time;
							case 10:// Tổng số phút về sóm
								 $time = $cell->getCalculatedValue();
								 $val["vesom"] = empty($time)?"":$time;
						}
				 }
				   if(!empty($val)and is_numeric($val["stt"]))
				   {	
					  $id_em =  $val["id_em"];
					  unset($val["stt"]);
					  unset($val["id_em"]);
					  $list[$id_em][] = $val;						 
				   }
				}
				$this->create_excel_room($list,$year,$month,$day,$day_banle);
			}
		}
	}
	function create_excel_room($list="",$year="",$month="",$day,$day_banle)
	{
		$this->load->library("excel");
		$list_room = $this->im_salary->list_em_room();
		$gio  = 0;
		$phut = 0;
		if(!empty($list_room))foreach($list_room as $item)
		{
				if(!empty($item["employ"]) and count($item["employ"]) > 0 and $item["employ"] > 0){
					$i = 0;
					$status = 0;
					$objPHPExcel = new PHPExcel();
					foreach(array_filter($item["employ"]) as $sub_item) // duyet nhan vien
					{ 
						if(!empty($sub_item["id2"]) and !empty($list[$sub_item["id2"]]) and count($list[$sub_item["id2"]]) > 0)
						{
							$objPHPExcel->setActiveSheetIndex($i);
							$objPHPExcel->getActiveSheet()->setTitle($list[$sub_item["id2"]][0]["name_em"]);
							$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
							$this->config_title_excel($objPHPExcel,$month,$year);// cau hinh title excel
							$row = 5;$total_phep = 0;
							$total_kophep = 0;
							$total_jobday = 0;
							$total_pt = 0;
							$total_ps = 0;
							$time = "";
							$num_phep = 0;
							$num_khongphep = 0;
							$day_vet = 1;	// vet cua ngày trước
							if($sub_item["room_id"] == 6)
								$date_quidinh  = $day_banle;
							else
								$date_quidinh = $day;
							$minute_t_flag = 0;// = 1 so phu di tre ve som tru theo khaoang 1 (-1 ngay cong)
							$day_flag = 0 ;
							$count_sunday = 0;
							foreach($list[$sub_item["id2"]] as $item_excel)
							{
								$gio_tre= 0;$phut_tre=0;$gio_som=0;$phut_som=0;$time_start="";$time_end="";$flag=0;
								$date_job = strtotime($this->change_month_to_day($item_excel["day_job"]));
								$day_flag = date("d",$date_job);
								if($day_flag - $day_vet > 1){ // neu no khong co ngay trong excel, se tu dong phat sinh,do tu dong them vao
									for($j=($day_flag-$day_vet-1);$j>=1;$j--){
										$date_itemexcel = $day_flag-$j."/".date("m",$date_job)."/".date("Y",$date_job);
										$item_excel_noday  = array("name_em"=>$item_excel["name_em"],"day_job"=>$date_itemexcel,"ca"=>$item_excel["ca"],"come_time"=>"N/A","out_time"=>"N/A","total_job"=>$item_excel["total_job"],"ditre"=>"","vesom"=>"");
										$date_job_noday = strtotime($this->change_month_to_day($date_itemexcel));
										$this->access_showexcel($day,$day_banle,$date_job_noday,$year,$month,$sub_item,$row,$item_excel_noday,$objPHPExcel);
										$total_pt 	  	+= $this->list_accessexcel["total_pt"];
										$total_ps 	  	+= $this->list_accessexcel["total_ps"];
										$total_jobday 	+= $this->list_accessexcel["total_jobday"];
										$num_phep     	+= $this->list_accessexcel["num_phep"];	
										$num_khongphep 	+= $this->list_accessexcel["num_khongphep"];


										if($minute_t_flag == 0)
											$minute_t_flag = $this->list_accessexcel["minute_t_flag"];
										$row++;
									}
								}
								$this->access_showexcel($day,$day_banle,$date_job,$year,$month,$sub_item,$row,$item_excel,$objPHPExcel);
								$total_pt 	  	+= $this->list_accessexcel["total_pt"];
								$total_ps 	  	+= $this->list_accessexcel["total_ps"];
								$total_jobday 	+= $this->list_accessexcel["total_jobday"];
								$num_phep     	+= $this->list_accessexcel["num_phep"];	
								if(trim($item_excel["thu"])=="CN"){
									$count_sunday++;
								}
								$num_khongphep 	+= $this->list_accessexcel["num_khongphep"];
								if($minute_t_flag == 0)
									$minute_t_flag = $this->list_accessexcel["minute_t_flag"];
								$day_vet = $day_flag;
								$row ++;
								$ca_lamviec = $item_excel["ca"];  
								
							}// ket thuc for duyet danh danh sách excel
							$status = 1;
							// Tong cong
							$objPHPExcel->getActiveSheet()->mergeCells('H'.$row.':I'.$row);
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,"Tổng cộng");
							$total_phuttre = ($total_pt == 0)?"00:00":floor($total_pt/60).":".$total_pt%60;
							$total_phutsom = ($total_ps == 0)?"00:00":floor($total_ps/60).":".$total_ps%60;	
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$row,$total_phuttre);
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$row,$total_phutsom);
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row,$total_jobday." (Ngày làm)");
							$minute_t = $total_pt+$total_ps;
							if($minute_t <=260){ // tru theo khoang 1
								if($minute_t_flag == 1)
									$type_minute_t = 2; // tru 1 lan khi di tre hon 10 phut
								else
									$type_minute_t = 1;
							}
							elseif($minute_t > 260 && $minute_t <=320) // tru theo khoang 2
								$type_minute_t = 3;
							else if($minute_t > 320 && $minute_t <=380) // tru theo khoang 3
								$type_minute_t = 4;
							else if($minute_t >= 381) // tru theo khoang 4 qui dinh
								$type_minute_t = 5;
							else
								$type_minute_t = 0;
							$num_khongphep = $num_khongphep-$count_sunday;
							$data = array("date_s"=>$num_phep,"date_n"=>($num_khongphep>0)?$num_khongphep:"","minute_t"=>$minute_t,"minute_s"=>$num_phep*480,"minute_n"=>$num_khongphep*480,"type_minute_t"=>$type_minute_t);
							$this->im_salary->update_salary($item["id"],$sub_item["id"],$month,$year,$data);// update ban emp_salary_năm	
													
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,$row,$total_phep);
							if($ca_lamviec != "HC")
								$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row+1,$day_banle-$total_jobday."( Ngày nghỉ)");
							else
								$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row+1,$day-$total_jobday."( Ngày nghỉ)");								
							$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row+1,(string)$sub_item["id2"]);
						}
						if(!empty($sub_item["id2"]) and !empty($list[$sub_item["id2"]]))
						{
							$objPHPExcel->createSheet();
							$i++;
						}
					}// ket thuc for duyet nhan vien
					if($status == 1)
					{
						$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
						$objWriter->save("./uploads/bang_cham_cong/".$year."/".$month."/".$year."_".$month."_".$item["id"].".xls");
						chmod("./uploads/bang_cham_cong/".$year."/".$month."/".$year."_".$month."_".$item["id"].".xls", octdec(777));
					}
			}
		}// ke thuc dong for duyet phong ban
	}
	function access_showexcel($day,$day_banle,$date_job,$year,$month,$sub_item,$row,$item_excel,$objPHPExcel){

		$total_jobday = 0;$num_phep = 0;$num_khongphep =0;$minute_t_flag=0;
		$day_sabbatical = $this->im_salary->list_sabbatical($year,$month,$date_job,$sub_item["id"]); 
		if(!empty($day_sabbatical)){				
			$day_sabbatical["value"]      = $day_sabbatical["value"];
			$day_sabbatical["type_off"]   = $day_sabbatical["type_off"];
			$day_sabbatical["salary"] 	= $day_sabbatical["salary"];
			if($day_sabbatical["salary"] == 1)
				$total_phep = $day_sabbatical["value"];
		}	
		$gio_tre  	= (!empty($item_excel["ditre"]) and $item_excel["ditre"] != 0)?floor($item_excel["ditre"]/60):0;// tong so gio di tre trong 1 ngày
		$phut_tre 	= (!empty($item_excel["ditre"]) and $item_excel["ditre"] != 0)?$item_excel["ditre"]%60:0;// tổng số phút di trễ trong 1 ngày

		$gio_som  	= (!empty($item_excel["vesom"])and $item_excel["vesom"] != 0)?floor($item_excel["vesom"]/60):0;// tổng số gio ve sớm trong 1 ngày
		$phut_som 	= (!empty($item_excel["vesom"])and $item_excel["vesom"] != 0)?$item_excel["vesom"]%60:0;// tổng phút về sớm tróng ngày
		if($item_excel["ca"] == "HC"){
			$gio_tre  	= (!empty($item_excel["ditre"]) and $item_excel["ditre"] != 0)?floor($item_excel["ditre"]/60):0;// tong so gio di tre trong 1 ngày
			$phut_tre 	= (!empty($item_excel["ditre"]) and $item_excel["ditre"] != 0)?$item_excel["ditre"]%60:0;// tổng số phút di trễ trong 1 ngày
								  	
			$gio_som  	= (!empty($item_excel["vesom"])and $item_excel["vesom"] != 0)?floor($item_excel["vesom"]/60):0;// tổng số gio ve sớm trong 1 ngày
			$phut_som 	= (!empty($item_excel["vesom"])and $item_excel["vesom"] != 0)?$item_excel["vesom"]%60:0;// tổng phút về sớm tróng ngày
								  	
			$total_pt 	= (!empty($item_excel["ditre"])and $item_excel["ditre"] != 0)?$item_excel["ditre"]:0;// tổng số gio ve sớm trong 1 ngày
			$total_ps 	= (!empty($item_excel["vesom"])and $item_excel["vesom"] != 0)?$item_excel["vesom"]:0;// tổng số gio ve sớm trong 1 ngày
		}
		else{
			$time_somtre = $this->access_time($item_excel,$day_banle);
			$gio_tre  	= (!empty($time_somtre["hour_tre"]) and $time_somtre["hour_tre"] != 0)?floor($time_somtre["hour_tre"]/60):0;// tong so gio di tre trong 1 ngày
			$phut_tre 	= (!empty($time_somtre["minute_tre"]) and $time_somtre["minute_tre"] != 0)?$time_somtre["minute_tre"]%60:0;// tổng số phút di trễ trong 1 ngày
	
			$gio_som  	= (!empty($time_somtre["hour_som"])and $time_somtre["hour_som"] != 0)?floor($time_somtre["hour_som"]/60):0;// tổng số gio ve sớm trong 1 ngày
			$phut_som 	= (!empty($time_somtre["minute_som"])and $time_somtre["minute_som"] != 0)?$time_somtre["minute_som"]%60:0;// tổng phút về sớm tróng ngày
						  
			$total_pt 	= (!empty($time_somtre["hour_tre"])and $time_somtre["hour_tre"] != 0)?floor($time_somtre["hour_tre"]/60).":".$time_somtre["minute_tre"]%60:0;// tổng số gio ve sớm trong 1 ngày
			$total_ps 	= (!empty($time_somtre["hour_som"])and $time_somtre["hour_som"] != 0)?floor($time_somtre["hour_som"]/60).":".$time_somtre["minute_som"]%60:0;// tổng số gio ve sớm trong 1 ngày
		}
		if(($total_pt+$total_ps)>10)
		   $minute_t_flag = 1;
		$phep = "";
	  	if(!empty($item_excel["come_time"]) and !empty($item_excel["out_time"]) and $item_excel["come_time"] != "N/A" and $item_excel["out_time"] != "N/A" )
		{
			$total_jobday = 1;
			$flag = 1; // xác đính nếu đã công thi không cộng nữa
		}
		else
		{
			if(!empty($day_sabbatical) && $day_sabbatical["type_off"] == 1 && $flag != 1 && $day_sabbatical["salary"] == 1)
			{
				$num_phep = 1;
				$phep = "Xin đi trễ";
				$total_jobday = 1;
			}
			else if(!empty($day_sabbatical) && $day_sabbatical["type_off"] == 2 && $flag != 1 && $day_sabbatical["salary"] == 1)
			{
				$num_phep = 1;
				$phep = "Xin nghỉ theo giờ";
				$total_jobday = 1;
			}
			else if (!empty($day_sabbatical) &&$day_sabbatical["type_off"] == 0 && !empty($day_sabbatical) && $flag != 1  && $day_sabbatical["salary"] == 1)
			{
				$num_phep = 1;
				$phep = "Xin về sớm";
				$total_jobday = 1;
			}
			else if (!empty($day_sabbatical) &&$day_sabbatical["type_off"] == 3 && !empty($day_sabbatical) && $flag != 1  && $day_sabbatical["salary"] == 1)
			{
				$num_phep = 1;
				$phep = "Xin nghi phép ngày";
				$total_jobday = 1;
			}
			else if(!empty($day_sabbatical) && $day_sabbatical["salary"] == 0 && $day_sabbatical["status"] == 1){
				$phep = "Xin nghi được duyệt, không có lương";
				$num_khongphep = 1;
			}
			else if(!empty($day_sabbatical) && $day_sabbatical["salary"] == 0){
				$phep = "Xin nghi chưa được duyệt";
				$num_khongphep = 1;
			}
			else
				$num_khongphep = 1;				
	   }
	   	if(empty($item_excel["come_time"]) and empty($item_excel["out_time"]))
			$phep = "Nghỉ không phép";
	    else if(!empty($item_excel["come_time"]) and $item_excel["out_time"] == "N/A" and $phep == "")
		{
			 $phep = "Không bấm giờ ra";
		}
		
		$time_start = !empty($day_sabbatical["time_start"])?date("d/m/Y H:i",$day_sabbatical["time_start"]):"";
		$time_end   = !empty($day_sabbatical["time_end"])?date("d/m/Y H:i",$day_sabbatical["time_end"]):"";										
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$row,$item_excel["name_em"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$row,$item_excel["day_job"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$row,$phep);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,$row,$time_start);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8,$row,$time_end);
		if(!empty($item_excel["come_time"]) and $item_excel["come_time"] != "N/A")
		{
		   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$row,$item_excel["come_time"]);
		   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,$row,$item_excel["out_time"]);
		   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$row,$gio_tre.":".$phut_tre);
		   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$row,$gio_som.":".$phut_som);
		   
		}// ket thuc kiem tra có bấm gio hay không
		else
		{
		  $objPHPExcel->getActiveSheet()->getStyle('C'.$row.':'.'F'.$row)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'd8e4bc'))));
		}
		if(date("l",$date_job) == "Sunday"){
			  $objPHPExcel->getActiveSheet()->getStyle('C'.$row.':'.'F'.$row)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'd8e4bc'))));
		}
		$row ++;
		$ca_lamviec = $item_excel["ca"];
		$this->list_accessexcel= array("total_pt"=>$total_pt,"total_ps"=>$total_ps,"total_jobday"=>$total_jobday,"num_phep"=>$num_phep,"num_khongphep"=>$num_khongphep,"minute_t_flag"=>$minute_t_flag);
		return $objPHPExcel;
	}// end xu ly lay du lieu xuet excel
	function config_title_excel($objPHPExcel,$month,$year)
	{
		$objPHPExcel->getActiveSheet()->getStyle('A36:I36')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00'))));
		 // chieu rong cua cot
		 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('27');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth('20');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth('14');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth('14');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth('14');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth('14');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth('22');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth('22');
		 $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth('22');
		 $objPHPExcel->getActiveSheet()->getStyle('A1:I36')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		 $objPHPExcel->getActiveSheet()->getStyle('A2:L2')->getBorders()->getAllBorders()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
		 for($i=1;$i<=36;$i++)
		 {
		   $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight('20');
		 }
	  $title1 = "PH-SMARTSTORE";
	  $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
	  $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFont()->setSize(15);
	  $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFont()->setBold(true);
	  $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	  $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	  $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(50);
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,1, $title1);
	  
	  $title2 = "Địa chỉ: 288 Đường 3 tháng 2, Phường 14, Quận 10";
	  $objPHPExcel->getActiveSheet()->mergeCells('F1:I1');
	  $objPHPExcel->getActiveSheet()->getStyle('F1:I1')->getFont()->setSize(15);
	  $objPHPExcel->getActiveSheet()->getStyle('F1:I1')->getFont()->setBold(true);
	  $objPHPExcel->getActiveSheet()->getStyle('F1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	  $objPHPExcel->getActiveSheet()->getStyle('F1:I1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	  $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(50);
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,1, $title2);
	  
	  $title3 = "Bảng chấm công tháng ".$month."/".$year;
	  $objPHPExcel->getActiveSheet()->mergeCells('A2:I2');
	  $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getFont()->setSize(15);
	  $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getFont()->setBold(true);
	  $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	  $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	  $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(70);
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,2,$title3);
	  // mer cel
	  $objPHPExcel->getActiveSheet()->mergeCells('A3:A4');
	  $objPHPExcel->getActiveSheet()->mergeCells('B3:B4');
	  $objPHPExcel->getActiveSheet()->mergeCells('C3:C4');
	  $objPHPExcel->getActiveSheet()->mergeCells('D3:D4');
	  $objPHPExcel->getActiveSheet()->mergeCells('E3:E4');
	  $objPHPExcel->getActiveSheet()->mergeCells('F3:F4');
	  $objPHPExcel->getActiveSheet()->mergeCells('G3:G4');
	  $objPHPExcel->getActiveSheet()->mergeCells('H3:I3');
	  // gan title
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,  3,  "Tên nhân viên");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,  3,  "Ngày làm");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,  3,  "Giờ vào");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,  3,  "Giờ ra");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,  3,  "Giờ đi trễ");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,  3,  "Giờ về sớm");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,  3,  "Ghi chú");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,  3,  "Ngày xin phép");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,  4,  "Từ ngày");
	  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8,  4,  "Đến ngày");
	  $objPHPExcel->getActiveSheet()->getStyle('A3:G3')->getFont()->setBold(true);	
	  // chu thich them
	  return $objPHPExcel;
	}
	function test_url_file($year,$month)
	{
		$name_file = $year."_".$month;
		$url_file_year 		 =  "./uploads/bang_cham_cong/$year";
		$url_file_year_month =  "./uploads/bang_cham_cong/$year/$month";
		$url_xls			 =  $url_file_year_month."/".$name_file.".xls";
		$url_xlsx			 =  $url_file_year_month."/".$name_file.".xlsx";
		if(!is_file($url_xls) and !is_file($url_xlsx))
			echo 0;
		else
			echo 1;	
	}
	function insert_salary($user_id, $month, $year){
		$this->salary->insert_salary($user_id, $month, $year);
	}
	function change_month_to_day($string_dayjob)
	{
		$len_string = strlen($string_dayjob);
		$flag_day = 0;$flag_month = 0;$flag_year = 0;$year_for =0;$month_for=0;
		$day_lamviec= "";$month_lamviec = "";$year_lamviec = "";
		for($date_job=0;$date_job<$len_string;$date_job++)
		{
			if($string_dayjob[$date_job] == "/"){
				$flag_day = 1;
				$month_for = 1;
			}
			if($flag_day == 0 and $string_dayjob[$date_job] != "/")
				$day_lamviec .= $string_dayjob[$date_job];
			if($month_for == 1){
				if($string_dayjob[$date_job] == "/" and $date_job > 3)
				{
					$flag_month = 1;
					$year_for = 1;
				}
				if($flag_month == 0 and $string_dayjob[$date_job] != "/")
				{
					$month_lamviec .= $string_dayjob[$date_job];
				}
			
			}
			if($year_for == 1)
			{
				if($string_dayjob[$date_job] != "/")					
				$year_lamviec .= $string_dayjob[$date_job];
			}
		}
		return trim($year_lamviec."/".$month_lamviec."/".$day_lamviec);
	}
	public function import_deduction() // import các khoang trừ  từ excel
	{
		if($_SERVER["REQUEST_METHOD"] == "POST"){
			$file_phucap = $this->upload_file("phucap","file");
			$file_congno = $this->upload_file("congno","file_congno");
			$post = $this->input->post();
			if(!empty($file_phucap)){
				$this->load->helper("excel");
				$url_file = "./uploads/file_deduction/".$file_phucap;
				$data 	  = array("id2"=>14,"total_price"=>12);
				if(file_exists($url_file)){
					$list_deduction = Read_excel($url_file,$data,0,6,1,15);	
				}
				
				if(!empty($list_deduction) and count($list_deduction)>0)
				{
					for($i=1;$i<=count($list_deduction);$i++)
					{
						if(!empty($list_deduction[$i]["id2"]) && !empty($list_deduction[$i]["total_price"])){
							$data = array("salary_add5"=>$list_deduction[$i]["total_price"]);
							
							$this->im_salary->update_deduction($post["year"],$post["month"],$list_deduction[$i]["id2"],0,$data);
						}
					}
					@unlink($url_file);	
					$add_phucap = "Thêm file công nợ thành công";
				}	
				else
					$add_phucap = "Xảy ra lỗi trong quá trình thêm file công nợ";	
			}
			else
			{
				$add_phucap = "Xảy ra lỗi trong quá trình thêm file công nợ";
				echo "<script> alert('".$add_phucap."');window.location='".site_url($this->uri->uri_string())."' </script>";exit;
			}
			if(!empty($file_congno)){
				$this->load->helper("excel");
				$url_file = "./uploads/file_deduction/".$file_congno;
				$data 	  = array("id2"=>1,"total_price"=>8,"tamung"=>4);
				$list_deduction = Read_excel($url_file,$data,0,6,1,11);
				if(!empty($list_deduction) and count($list_deduction)>0)
				{
					for($i=1;$i<=count($list_deduction);$i++)
					{
						if(!empty($list_deduction[$i]["id2"]) && !empty($list_deduction[$i]["total_price"])){
							$tamung = !empty($list_deduction[$i]["tamung"])?$list_deduction[$i]["tamung"]:0;
							$data = array("except4"=>$list_deduction[$i]["total_price"],"except5"=>$tamung);
							$this->im_salary->update_deduction($post["year"],$post["month"],$list_deduction[$i]["id2"],1,$data);
						}
					}
					@unlink($url_file);	
					$add_phucap = "Thêm file thành công";
				}	
			}
			else{
				$add_phucap = "Xảy ra lỗi trong quá trình thêm file phụ cấp";
				echo "<script> alert('".$add_phucap."');window.location='".site_url($this->uri->uri_string())."' </script>";exit;
			}
			
			echo "<script> alert('".$add_phucap."');window.location='".site_url($this->uri->uri_string())."' </script>";exit;
		}
		else
		{
			$month_select = $this->number_m->month_select();
			$year_select  = $this->number_m->year_select($this->year_start,date("Y"));
			$this->action = 'back|'.site_url('emp_profile/salary').'|'.lang('global:back');
			$this->template->set('admin',true)->append_css('toolbar.css');
			$this->template ->set("title",lang('salary:add_table_salary'))
					->set("action",$this->action)
					->set("month_select",$month_select)
					->set("year_select",$year_select)
					->append_css("import_salary.css")
					->append_css('table.css')
					->append_js('ui/jquery-ui.min.js')
					->append_css('ui/jquery-ui.min.css')
					->append_js('admin/plugins.js')
					->append_js('admin/scripts.js')
					->append_js('business/site.js')
					->append_js('chosen/chosen.jquery.min.js')
					->append_css('chosen/chosen.css')
					->build($this->controller."/form_deduction");	
		}
	}
	private function upload_file($name_file,$name_input)
	{
		$this->load->library('upload');
		if(!is_dir("./uploads/file_deduction/"))
		{
			$mode = 777;
			@mkdir("./uploads/file_deduction/",0777);
			@chmod("./uploads/file_deduction/", octdec($mode));
		}
		$config['upload_path'] = "./uploads/file_deduction/";
		$config['allowed_types'] = 'xlsx|xls';
		$config['max_size']	= '10000';	
		$config['file_name'] = $name_file;
		$this->upload->initialize($config);
		if($this->upload->do_upload($name_input)){
			$data_file = $this->upload->data();
			return $data_file["file_name"];
		}	
		else
			return 0;
		
	}
	function access_time($item_excel,$date_work)
	{
		$time = "";
		$time["time_tre"] = "00:00";
		$time["time_som"] = "00:00";
		if(!empty($item_excel["come_time"]))
		{
			if(strtotime($item_excel["come_time"]) < strtotime("13:00")){ // phong ban le ca 1
				$time_tre = strtotime($item_excel["come_time"]) - $this->hourscome_banle_ca1;
				$time["time_tre"] = ($time_tre > 0)?date("H",$time_tre)-1 .":".date("i",$time_tre):""; 
				$time["minute_tre"] = date("i",$time_tre);
				$time["hour_tre"]	= date("H",$time_tre);
				$time_som = $this->hoursout_banle_ca1 - strtotime($item_excel["out_time"]);
				$time["time_som"] = ($time_som > 0)?date("H",$time_som)-1 .":".date("i",$time_som):"";
				$time["minute_som"] = date("i",$time_tre);
				$time["hour_som"]	= date("H",$time_tre);
			}
			else{ // phòng bán lẻ ca 2
				$time_tre = strtotime($item_excel["come_time"]) - $this->hourscome_banle_ca2;
				$time["time_tre"] = ($time_tre > 0)?date("H",$time_tre)-1 .":".date("i",$time_tre):""; 
				$time["minute_tre"] = ($time_tre > 0)?date("i",$time_tre):0;
				$time["hour_tre"]	= ($time_tre > 0)?date("H",$time_tre):0;
				$time_som = $this->hoursout_banle_ca2 - strtotime($item_excel["out_time"]);
				$time["time_som"] 	= ($time_som > 0)?date("H",$time_som)-1 .":".date("i",$time_som):"";
				$time["minute_som"] = ($time_som > 0)?date("i",$time_som):0;
				$time["hour_som"]	= ($time_som > 0)?date("H",$time_som):0;
			}
		}
		return $time;
	}
	function delete_salary(){
		if($_SERVER["REQUEST_METHOD"] == "GET"){
			$this->load->model("emp_profile/salary_m","salary_m");
			$post = $this->input->get();
			$this->db->where("month",$post["month"]);
			$this->db->delete("emp_salary_".$post["year"]);
			$this->salary_m->check_and_insert($post["month"],$post["year"]);
			echo "<script>alert('Cập nhật thành công');window.location='".site_url("emp_profile/import_salary/active_salary")."'</script>";exit;
		}
		echo "<script>alert('Hiện không có bảng lương');window.location='".site_url("emp_profile/import_salary/active_salary")."'</script>";exit;
	}
}